package com.neuedu.oasystem.dao.impl;
/**
 * @author QXT
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.neuedu.oasystem.dao.ICustomerDao;
import com.neuedu.oasystem.entity.Customer;
import com.neuedu.oasystem.util.DBHelper;

public class CustomerDaoImpl extends BaseDao implements ICustomerDao {

	@Override
	public boolean add(Customer param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "insert into customer(customer_Id,customer_Name,customer_Position,customer_Sex,customer_Unit,customer_Address,customer_Phone,customer_Email) values(?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, param.getCustomerId());
			stmt.setString(2, param.getCustomerName());
			stmt.setString(3, param.getCustomerPosition());
			stmt.setString(4, param.getCustomerSex());
			stmt.setString(5, param.getCustomerUnit());
			stmt.setString(6, param.getCustomerAddress());
			stmt.setString(7, param.getCustomerPhone());
			stmt.setString(8, param.getCustomerEmail());
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return false;
	}

	@Override
	public boolean edit(Customer param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "update customer set customer_Name=?,customer_Position=?,customer_Sex=?,customer_Unit=?,customer_Address=?,customer_Phone=?,customer_Email=? where customer_Id=? ";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(8, param.getCustomerId());
			stmt.setString(1, param.getCustomerName());
			stmt.setString(2, param.getCustomerPosition());
			stmt.setString(3, param.getCustomerSex());
			stmt.setString(4, param.getCustomerUnit());
			stmt.setString(5, param.getCustomerAddress());
			stmt.setString(6, param.getCustomerPhone());
			stmt.setString(7, param.getCustomerEmail());
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return false;
	}

	@Override
	public boolean deleteById(int id) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;

		try {
			String sql = "delete from customer where customer_Id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}

		return false;
	}

	@Override
	public Customer findById(int id) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		Customer customer = null;
		ResultSet rs = null;

		try {

			String sql = "select customer_Id,customer_Name,customer_Position,customer_Sex,customer_Unit,customer_Address,customer_Phone,customer_Email from customer where customer_Id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1,id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				int customerId = rs.getInt("customer_Id");
				String customerName = rs.getString("customer_Name");
				String customerPosition = rs.getString("customer_Position");
				String customerSex = rs.getString("customer_Sex");
				String customerUnit = rs.getString("customer_Unit");
				String customerAddress = rs.getString("customer_Address");
				String customerPhone = rs.getString("customer_Phone");
				String customerEmail = rs.getString("customer_Email");
				customer = new Customer(customerId, customerName,
						customerPosition, customerSex, customerUnit,
						customerAddress, customerPhone, customerEmail);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return customer;
	}

	@Override
	public List<Customer> findAll() {
		List<Customer> list = new ArrayList<Customer>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			String sql = "select customer_Id,customer_Name,customer_Position,customer_Sex,customer_Unit,customer_Address,customer_Phone,customer_Email from customer ";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {

				int customerid = rs.getInt("customer_Id");
				String customerName = rs.getString("customer_Name");
				String customerPosition = rs.getString("customer_Position");
				String customerSex = rs.getString("customer_Sex");
				String customerUnit = rs.getString("customer_Unit");
				String customerAddress = rs.getString("customer_Address");
				String customerPhone = rs.getString("customer_Phone");
				String customerEmail = rs.getString("customer_Email");
				Customer customer = new Customer(customerid, customerName,
						customerPosition, customerSex, customerUnit,
						customerAddress, customerPhone, customerEmail);
				list.add(customer);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}

		return list;
	}

	@Override
	public List<Customer> findByExample(Customer param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		List<Customer> list = new ArrayList<Customer>();
		ResultSet rs = null;
		try {
			String sql = "select customer_Id,customer_Name,customer_Position,customer_Sex,customer_Unit,customer_Address,customer_Phone,customer_Email from customer where customer_Name like ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, "%" + param.getCustomerName() + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				int customerId = rs.getInt("customer_Id");
				String customerName = rs.getString("customer_Name");
				String customerPosition = rs.getString("customer_Position");
				String customerSex = rs.getString("customer_Sex");
				String customerUnit = rs.getString("customer_Unit");
				String customerAddress = rs.getString("customer_Address");
				String customerPhone = rs.getString("customer_Phone");
				String customerEmail = rs.getString("customer_Email");
				Customer customer = new Customer(customerId, customerName,
						customerPosition, customerSex, customerUnit,
						customerAddress, customerPhone, customerEmail);
				list.add(customer);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}

		return list;
	}

	@Override
	public List<Customer> findByPager(int page, int size) {
		List<Customer> list = new ArrayList<Customer>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select * from"
					+ "(select rownum rn,customer_Id,customer_Name,customer_Position,customer_Sex,customer_Unit,customer_Address,customer_Phone,customer_Email from customer where rownum<=?)where rn>? ";

			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, page * size);
			stmt.setInt(2, (page - 1) * size);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int customerId = rs.getInt("customer_Id");
				String customerName = rs.getString("customer_Name");
				String customerPosition = rs.getString("customer_Position");
				String customerSex = rs.getString("customer_Sex");
				String customerUnit = rs.getString("customer_Unit");
				String customerAddress = rs.getString("customer_Address");
				String customerPhone = rs.getString("customer_Phone");
				String customerEmail = rs.getString("customer_Email");
				Customer customer = new Customer(customerId, customerName,
						customerPosition, customerSex, customerUnit,
						customerAddress, customerPhone, customerEmail);
				list.add(customer);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return list;
	}

	@Override
	public int deleteByIds(int[] ids) {
		int result = 0;
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = "delete from customer where customer_Id = ?";
			stmt = conn.prepareStatement(sql);
			for (int customerId : ids) {
				stmt.setInt(1, customerId);
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == -2) {
					result++;
				}
			}
			if (result == ids.length) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if (conn != null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}

			}
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return result;
	}

	@Override
	public int addAll(List<Customer> useparamrs) {
		int result = 0;
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = "insert into customer (customer_Id,customer_Name,customer_Position,customer_Sex,customer_Unit,customer_Address,customer_Phone,customer_Email) values (?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
			for (Customer i : useparamrs) {
				stmt.setString(1, i.getCustomerName());
				stmt.setString(2, i.getCustomerPosition());
				stmt.setString(3, i.getCustomerSex());
				stmt.setString(4, i.getCustomerUnit());
				stmt.setString(5, i.getCustomerAddress());
				stmt.setString(6, i.getCustomerPhone());
				stmt.setString(7, i.getCustomerEmail());
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == -2) {
					result++;
				}
			}
			if (result == useparamrs.size()) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if (conn != null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return result;
	}

	@Override
	public int editAll(List<Customer> param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		int result = 0;
		try {
			conn.setAutoCommit(false);
			String sql = "update customer set customer_Name=?,customer_Position=?,customer_Sex=?,customer_Unit=?,customer_Address=?,customer_Phone=?,customer_Email=? where customer_Id=?";
			stmt = conn.prepareStatement(sql);
			for (Customer i : param) {
				stmt.setInt(8, i.getCustomerId());
				stmt.setString(1, i.getCustomerName());
				stmt.setString(2, i.getCustomerPosition());
				stmt.setString(3, i.getCustomerSex());
				stmt.setString(4, i.getCustomerUnit());
				stmt.setString(5, i.getCustomerAddress());
				stmt.setString(6, i.getCustomerPhone());
				stmt.setString(7, i.getCustomerEmail());
				stmt.addBatch();

			}
			int[] counts = stmt.executeBatch();
			for (int i : counts) {
				if (i == -2) {
					result++;
				}
			}
			if (result == param.size()) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();

			}

		} catch (SQLException e) {
			e.printStackTrace();
			if (conn != null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}

			}
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}

		return result;
	}

}
